package com.intertid.sz.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import com.intertid.sz.WS.Pagination;
import com.intertid.sz.WS.szxwC;
import com.intertid.sz.WS.szxwWS;




@Repository
public class SzXwDAO {

	
	@Autowired
	NamedParameterJdbcTemplate ECMNamedJdbcTemplate;
	public Pagination<szxwWS> SxD(String chanIds,int status,String ipAddress,int pageSize, int pageIndex){

		List<szxwWS> list = new ArrayList<szxwWS>();
		Pagination<szxwWS> page =new Pagination<szxwWS>();
		page.setPageIndex(pageIndex);
		page.setPageSize(pageSize);
	     int totalCount=0;
		Map<String, Object> map = new HashMap<String, Object>();
		String sql="";
		if(status==1){
		sql ="select count(*) from cms_channel a LEFT JOIN cms_chan_doc b ON a.id=b.chan_id LEFT JOIN cms_document c on b.doc_id=c.id WHERE a.parent_id in (" +chanIds+ ") and b.status = 4 "
				+ " and c.doc_content <>'' ORDER BY b.write_time desc";
		}else{
			
			sql ="select count(*) from cms_channel a LEFT JOIN cms_chan_doc b ON a.id=b.chan_id LEFT JOIN cms_document c on b.doc_id=c.id WHERE a.id in (" +chanIds+ ") and b.status = 4 "
					+ "and c.doc_content <>'' ORDER BY b.write_time desc";
		}
		totalCount = ECMNamedJdbcTemplate.queryForInt(sql, map);
		
		if(totalCount>0){
			page.setTotalCount(50);
			int pageCount = 0;
			// 计算总页数
			if ((totalCount % pageSize) > 0) {
				pageCount = (totalCount / pageSize) + 1;
			} else {
				pageCount = totalCount / pageSize;
			}
		
			page.setPageCount(pageCount);
		}
		
		
		int startIndex = (pageIndex - 1) * pageSize;// 分页开始记录
		int endIndex = pageIndex * pageSize-1;
		map.put("startIndex", startIndex);
		map.put("endIndex", endIndex);
		
		String sqls="";
		if(status==1){
		sqls ="select * from cms_channel a LEFT JOIN cms_chan_doc b ON a.id=b.chan_id LEFT JOIN cms_document c on b.doc_id=c.id WHERE a.parent_id in (" +chanIds+ ") and b.status = 4 "
				+ " and c.doc_content <>'' ORDER BY b.write_time desc limit :startIndex,:endIndex";
		}else{
			
			sqls ="select * from cms_channel a LEFT JOIN cms_chan_doc b ON a.id=b.chan_id LEFT JOIN cms_document c on b.doc_id=c.id WHERE a.id in (" +chanIds+ ") and b.status = 4 "
					+ "and c.doc_content <>'' ORDER BY b.write_time desc limit :startIndex,:endIndex";
		}
		
		list=ECMNamedJdbcTemplate.query(sqls, map,new ResultSetExtractor<List<szxwWS>>(){
          	public List<szxwWS> extractData(ResultSet rs) throws SQLException, DataAccessException {
          		List<szxwWS> lss=new ArrayList<szxwWS>();
          		while (rs.next()) {
          			szxwWS sW=new szxwWS();
          			sW.setContent(rs.getString("doc_content"));
          		    sW.setTitle(rs.getString("title"));
          		    sW.setId(rs.getString("doc_id"));
          		    sW.setPublishDate(rs.getString("write_time"));
          		    sW.setSource(rs.getString("doc_source"));
          		    sW.setUrl(rs.getString("url"));
          		    sW.setImage(rs.getString("pic_url"));
          		   
                      lss.add(sW);
					}
          		return lss;
          	}

          });
		 page.setIp(ipAddress);
		  page.setList(list);
		  
		
		
				return page;
				
				
		
	};
	
	
	public Pagination<szxwC> conCs(String docId,String ip) {
		Pagination<szxwC> page =new Pagination<szxwC>();
		
		 page.setIp(ip);
		 Map<String, Object> map =new HashMap<String, Object>(); 
		 
			    map.put("docId", docId);

		 String sqls ="select * from cms_chan_doc a left join cms_document c on a.doc_id=c.id where a.status = 4 and doc_id = :docId";
		 
		 List<szxwC> list = new ArrayList<szxwC>();
		 list=ECMNamedJdbcTemplate.query(sqls, map,new ResultSetExtractor<List<szxwC>>(){
	          	public List<szxwC> extractData(ResultSet rs) throws SQLException, DataAccessException {
	          		List<szxwC> lss=new ArrayList<szxwC>();
	          		
	          		while (rs.next()) {
	          			szxwC sWss=new szxwC();
	          			sWss.setContent(rs.getString("doc_content"));
	          		    sWss.setTitle(rs.getString("title"));
	          		    sWss.setId(rs.getString("doc_id"));
	          		    sWss.setPublishDate(rs.getString("write_time"));
	          		    sWss.setSource(rs.getString("doc_source"));
	          		    sWss.setUrl(rs.getString("url"));
	          		   sWss.setImage(rs.getString("pic_url"));
	                      lss.add(sWss);
						}
	          		
	          		return lss;
	          	}

	          });
		
		  page.setList(list);
		 return page;
	}
	
	
	
	
	
	
	
	
	
	public String XunH(String chanIds,String chanIdss){
		Map<String, Object> map = new HashMap<String, Object>();
		String s="";
		chanIdss +=","+chanIds;
		

		 s="select id from cms_channel WHERE parent_id in(" +chanIds+ ")";
		List<String> list=new ArrayList<String>();
		  list=ECMNamedJdbcTemplate.query(s, map, new ResultSetExtractor<List<String>>(){
          	public List<String> extractData(ResultSet rs) throws SQLException, DataAccessException {
          		 List<String> lists =new ArrayList<String>();
          		 
          		 while (rs.next()) {
          			 lists.add(rs.getString("id"));           		
					}          		 
          		 return lists;
				}   	
          });
		  
		  if(null == list || list.size() ==0){
			  
			  return chanIdss;
		  }else{
			  String dd="";
			  for(int i = 0 ; i < list.size()-1 ; i++) {
				dd += list.get(i)+",";
				  
				} 
			  dd +=list.get(list.size()-1);
			  return  XunH(dd,chanIdss);
			   
		  }

		
		
		
		
	};
}
